//  Android Carpool System
//  2011-2011
//  Author: Jinhuan Wang
//  name:  DBOperation.java
//  description: provide methods to operate data base 
//$history: /carpoolservice/scr/com/carpool/service/DBOperation.java$
/*************************************************/
// add     confirm method to allow passage confirm his choice
// Author: Jinhuan Wang Date 2011-5-21
/*************************************************/
package com.carpool.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dataexchange.GeoPoint;
import com.dataexchange.MyGeoPoint;
import com.dataexchange.RouteInfo;
import com.dataexchange.d2sRequest;
import com.dataexchange.p2sRequest;
import com.dataexchange.postData;
import com.dataexchange.s2pReply;

class DBConnection {

	private final static String URL = "jdbc:sqlserver://";
	private final static String HOST = "localhost";
	private final static String PORT = "1433";
	private final static String DBNAME = "carpoolet";
	private final static String USER = "sa";
	private final static String PASSWORD = "90226644";

	public Connection getConnection() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL + HOST + ":" + PORT
					+ ";databaseName=" + DBNAME, USER, PASSWORD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}
}

public class DBOperation {
	private final static String DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private DBConnection dbc = new DBConnection();
	static {
		try {
			Class.forName(DRIVER);

		} catch (Exception ex) {
			System.err.println(ex.getMessage());
		}
	}

	public int insertRoute(postData Request) throws SQLException {
		int id = 0;// route id
		d2sRequest data = (d2sRequest) Request;
		Connection con = null;
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			String addroute = "insert into [routes](ownerid,capacity,requirement,remainder) values('"
					+ data.getUserID()
					+ "','"
					+ data.getCapacity()
					+ "','"
					+ data.getRequirement() + "','" + data.getCapacity() + "')";

			PreparedStatement pstmt = con.prepareStatement(addroute,
					Statement.RETURN_GENERATED_KEYS);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			if (rs.next()) {

				id = rs.getInt(1);

			}
			rs.close();
			pstmt.close();

			List<MyGeoPoint> list = data.getMyGeoPoints();
			for (int i = 0; i < list.size(); i++) {
				MyGeoPoint mp = list.get(i);
				StringBuilder sb = new StringBuilder(
						"insert into geopoints(routeid,Latitude,Longitude,Period)values('"
								+ id + "','" + mp.getlatitude() + "','"
								+ mp.getlongitude() + "','" + mp.getDate()
								+ "')");
				statement.execute(sb.toString());
			}
			statement.close();
			//update user state to driver
			StringBuilder sb = new StringBuilder("UPDATE [user]"
					+ " SET [state] = " + true + "WHERE userid='"
					+ data.getUserID() + "'");
			statement.execute(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}

		return id;
	}

	public s2pReply queryRoute(postData Request)   {
		s2pReply find = null;
		p2sRequest data = (p2sRequest) Request;
		int precision=3000;
		Connection con = null;
		ResultSet rs=null;
		int routeid;
		int userid=0;
		int startlon = data.getStartPoint().getlongitude();
		int startlat = data.getStartPoint().getlatitude();
		int endlon = data.getEndPoint().getlongitude();
		int endlat = data.getEndPoint().getlatitude();
		class SimpleRoute{
			private GeoPoint Start;
			private GeoPoint End;
			private int routeid;
			public SimpleRoute(GeoPoint Start,GeoPoint End,int routeid)
			{this.routeid=routeid;
			this.Start=Start;
			this.End=End;
			}
			public GeoPoint getStart(){
				return this.Start;
			}
			public GeoPoint getEnd(){
				return this.End;
			}
			public int getRouteId(){
				return this.routeid;
			}
		}
		List<SimpleRoute> routelist=new ArrayList<SimpleRoute>();
		
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			StringBuilder sql=new StringBuilder(
				"SELECT g3.routeid ,g3.Latitude,g3.Longitude,g4.Latitude ,g4.Longitude " +
					"from [geopoints]as g3, [geopoints]as g4,(SELECT g1.routeid as id1, "+
				"MIN (POWER((g1.Latitude-'"+startlat+"'),2)+POWER((g1.Longitude-'"+startlon+"'),2)) as dis1,"+
				"MIN(POWER((g2.Latitude-'"+endlat+"'),2)+POWER((g2.Longitude-'"+endlon+"'),2)) as dis2 "+
					"from [geopoints]as g1, [geopoints]as g2 "+
					"where (g1.Longitude between'"+(startlon-precision)+"'and'"+(startlon+precision)+"'"+
						"and g1.Latitude between'"+(startlat-precision)+"'and '"+(startlat+precision)+"'"+
						"and g2.Longitude between'"+(endlon-precision)+"'and'"+(endlon+precision)+"'"+
						"and g2.Latitude between'"+(endlat-precision)+"' and'"+(endlat+precision)+"'"+
						"and g1.routeid=g2.routeid and g1.pointid<g2.pointid)"+
					"group by g1.routeid"+
					") as  temp "+
			"where g3.routeid=temp.id1 and "+
				"POWER((g3.Latitude-'"+startlat+"'),2)+POWER((g3.Longitude-'"+startlon+"'),2)=dis1 and "+
				"POWER((g4.Latitude-'"+endlat+"'),2)+POWER((g4.Longitude-'"+endlon+"'),2)=dis2 and"+
				 "(g3.Longitude between'"+(startlon-precision)+"'and'"+(startlon+precision)+"')"+
						"and (g3.Latitude between'"+(startlat-precision)+"'and '"+(startlat+precision)+"')"+
						"and (g4.Longitude between'"+(endlon-precision)+"'and'"+(endlon+precision)+"')"+
						"and (g4.Latitude between'"+(endlat-precision)+"' and'"+(endlat+precision)+"')"+
						"and (g3.routeid=g4.routeid) and g3.pointid<g4.pointid");
			rs=statement.executeQuery(sql.toString());
			while(rs.next())
			{
				SimpleRoute sr =new SimpleRoute(new GeoPoint(rs.getInt(3), rs.getInt(2)), new GeoPoint(rs.getInt(5), rs.getInt(4)), rs.getInt(1));
				routelist.add(sr);
			}
			rs.close();
		}catch ( SQLException e){}
		try {
			
			find=new s2pReply();
			for(int i=0;i<routelist.size();i++)
			{ 
				routeid=routelist.get(i).getRouteId();
				RouteInfo route=new RouteInfo();
				route.setCStartPoint(routelist.get(0).getStart().getlongitude(), routelist.get(0).getStart().getlatitude());
				route.setCEndPoint(routelist.get(0).getEnd().getlongitude(), routelist.get(0).getEnd().getlatitude());
				route.setRouteID(routeid);
				con = this.dbc.getConnection();
				Statement statement = con.createStatement();
				StringBuilder sql=new StringBuilder("SELECT [ownerid],[requirement],[remainder]FROM [routes] WHERE routeid='"+routeid+"'");
				rs=statement.executeQuery(sql.toString());
			if(rs.next())
				{
				userid=rs.getInt(1);
				route.setDriverID(userid);
				route.setRemainder(rs.getInt(3));
				route.setRequirement(rs.getString(2));
				}
			
				con = this.dbc.getConnection();
			 statement = con.createStatement();
				sql=new StringBuilder("SELECT [username],[phone],[Credit],[rateTimes] FROM [user] WHERE userid='"+userid+"'");
				rs=statement.executeQuery(sql.toString());
				if(rs.next())
				{
					route.setUsername(rs.getString(1));
					route.setNumber(rs.getString(2));
					route.setCredit(Math.round(rs.getInt(3)/rs.getInt(4)));
				}
			
			find.addRouter(route);
			}
		}catch(Exception e){
			e.getStackTrace();
		}
		
		return find;
	}

	@SuppressWarnings("unchecked")
	public List login(String uname, String password) {
		List reply = null;
		Connection con = null;
		ResultSet rs = null;
		String searchUsr = null;
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			searchUsr = "SELECT userid,phone,state FROM [user] WHERE username='"
					+ uname + "' and password='" + password + "'";
			rs = statement.executeQuery(searchUsr);
			if (rs.next()) {
				reply = new ArrayList();
				reply.add(0, rs.getInt(1));
				reply.add(1, rs.getString(2));
				reply.add(2, rs.getBoolean(3));

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return reply;

	}

	public boolean register(String uname, String password, String cellphone)
			throws SQLException {
		boolean returnflag = false;
		Connection con = null;
		String insertUsr = null;
		ResultSet rs = null;
		boolean DEFAULTSTATE = false;// a new user's default type is passage
		int DEFAULTCREDIT = 0;
		String searchUsr = null;
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			searchUsr = "SELECT username FROM [user] WHERE username='" + uname
					+ "'";
			rs = statement.executeQuery(searchUsr);
			if (rs.next()) {
				return returnflag;

			}
			rs.close();
			insertUsr = "INSERT INTO  [user]  (username,phone ,password,Credit,state)VALUES('"
					+ uname
					+ "','"
					+ cellphone
					+ "','"
					+ password
					+ "','"
					+ DEFAULTCREDIT + "','" + DEFAULTSTATE + "')";

			statement.execute(insertUsr);
			returnflag = true;

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			con.close();
		}

		return returnflag;

	}


	public boolean confirm(int passagerID,int driverID, int routeID) {
		Connection con = null;
		String sql = null;
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			sql = "INSERT INTO [deal]( passagerID,driverID,routeID) VALUES ('"+
			passagerID+"','"+driverID+"','"+routeID+"')";
			statement.execute(sql);			
			sql = "UPDATE [routes]SET [remainder] =remainder-1  WHERE ownerid='"
				+ driverID + "'";
			statement.execute(sql);	
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean rate(int rate, int driverID) {
		Connection con = null;
		int preCredit=0;
		int rateTimes=1;
		String sql = null;
		ResultSet rs;
		try {
			con = this.dbc.getConnection();
			Statement statement = con.createStatement();
			sql ="SELECT [Credit],[rateTimes] FROM [user] WHERE userid='"+driverID+"'";
			rs=statement.executeQuery(sql);
			if(rs.next()){
				preCredit=rs.getInt(1);
				rateTimes=rs.getInt(2);
			}
			rateTimes++;
			preCredit+=rate;
			sql = "UPDATE [carpoolet].[dbo].[user] SET [Credit] = '"+preCredit+"',[rateTimes]='"+rateTimes+"' WHERE userid='"+driverID+"'"; 
			statement.execute(sql);						 
			return true;
		} catch (SQLException e) {		
			e.printStackTrace();
		}
		return false;
	}
}